package com.sztz.utils;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.sztz.constants.CommonConstants;
import com.sztz.entity.FirmCustomerAuxiliary;
import com.sztz.entity.FirmCustomerInfo;

import java.io.File;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * 批量导入客户信息
 * @author yzy
 * @date 2022/7/4 15:16
 */
public class FirmCustomerInfoUtils {
    /**
     * 读取提供的EXCEL文件，获取客户信息
     * @return List<FirmCustomerInfo> 列表数据
     * @author yzy
     * @date 2022/7/4 11:20
     */
    public static List<FirmCustomerInfo> readFirmCustomerExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220704\\勤达--客户供应商导入文件(1).xlsx")).head(FirmCustomerInfo.class).sheet().doReadSync();
    }
    /**
     * 读取已存在的客户信息
     * @return List<FirmCustomerInfo> 列表数据
     * @author yzy
     * @date 2022/7/4 11:20
     */
    public static List<FirmCustomerInfo> readExistsFirmCustomer(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220704\\data.xls")).head(FirmCustomerInfo.class).sheet().doReadSync();
    }
    /**
     * 组装插入客户信息的SQL脚本
     * @param customerInfoCode 客户代码
     * @param shortName 客户简称
     * @param fullName 客户全称
     * @param regNo 客户信用代码
     * @oaran customsCode 客户海关编码
     * @return String sql
     */
    public static String createFirmCustomerInfoSql(String customerInfoCode,String shortName,String fullName,String regNo,String customsCode){
        String sql = "INSERT INTO TZ_USERCENTER.FIRM_CUSTOMER_INFOS ( id, system_id, system_name, customer_id, customer_code, customer_short_name, customer_name_cn, customer_name_en, customer_fullname, customer_registered_no, customer_customs_code, customer_carrier_code, customer_carrier_name, customer_website, customer_legal_person, customer_country_name, customer_province_id, customer_province_name, customer_city_id, customer_city_name, customer_county_id, customer_county_name, customer_registered_addr, customer_office_addr, customer_postcode, customer_contacts, customer_tel, customer_fax, customer_description, customer_principal_email, supplier_id, supplier_code, supplier_short_name, supplier_name_cn, supplier_name_en, supplier_fullname, supplier_registered_no, supplier_description, customer_flag, supplier_flag, status, creater_id, creater_name, creater_org_id, creater_firm_id, create_time, modifier_id, modifier_name, modifier_firm_id, customs_man_id, customs_man, customs_group_id, customs_group_name, modify_time, customer_service_staff_id, customer_service_staff_name, implementer_id, implementer_name, customer_belong, is_customer, is_supplier )" +
                "VALUES ( UUID() ,'00000000-0000-0000-0000-000000000000','全链通平台（含金二业务）','00000000-0000-0000-0000-000000000000','{}','{}','{}','{}','{}','{}','{}','','','','','中国','','','','','','','','','','','','','','','FBE66F7B-E914-4583-99BB-389779F60992','MF','东莞勤达','东莞勤达报关有限公司','Dongguan Kinder Customs Declaration Co., Ltd.','东莞勤达报关有限公司','91441900MA4X81WK81','',1,0,8,'A4A8E5B3-6E65-41F9-85F9-F2D79DE356E7','勤达管理员','','FBE66F7B-E914-4583-99BB-389779F60992',now(),'A4A8E5B3-6E65-41F9-85F9-F2D79DE356E7','系统','FBE66F7B-E914-4583-99BB-389779F60992','','','','',now(),'','','','',0,1,0);";
        return StrUtil.format(sql,customerInfoCode,shortName,fullName,shortName, fullName,regNo,customsCode);
    }
    /**
     * 读取提供的EXCEL文件，获取客户信息转成SQL语句输出到文件中
     * @author yzy
     * @date 2022/7/4 19:20
     */
    public static void doCustomerInfoSql(){
        //获取数据库中已存在的客户信息
        List<FirmCustomerInfo> dbFirmList = readExistsFirmCustomer();
        //从Excel中解析出对应的客户信息
        List<FirmCustomerInfo> newFirmCustomers = readFirmCustomerExcel().stream().filter(
                firmCus -> dbFirmList.stream().noneMatch(
                        item-> StrUtil.equalsAnyIgnoreCase(StrUtil.trim(firmCus.getRegisteredNo()),StrUtil.trim(item.getRegisteredNo()))
                )
        ).collect(Collectors.toList());
        //生成三位的客户编码`
        List<String> randomCustomerCode = CommUtils.getRandomCustomerCode(newFirmCustomers.size());
        //组装sql语句
        List<String> sqlList = IntStream.range(0,newFirmCustomers.size()).mapToObj(x->{
            FirmCustomerInfo item = newFirmCustomers.get(x);
            return createFirmCustomerInfoSql(randomCustomerCode.get(x), StrUtil.trim(item.getShortName()), StrUtil.trim(item.getFullName()), StrUtil.trim(item.getRegisteredNo()), StrUtil.trim(item.getCustomsCode()));
        }).collect(Collectors.toList());
        //输入到一个sql文件中
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220704\\out\\customerInfo.sql");
        FileUtil.appendUtf8Lines(sqlList,new File(outFilePath));
    }
    /**
     * 读取提供的EXCEL文件，获取客户附属信息
     * @return List<FirmCustomerAuxiliary> 列表数据
     * @author yzy
     * @date 2022/7/5 19:20
     */
    public static List<FirmCustomerAuxiliary> readFirmCustomerAuxiliaryExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220704\\勤达--客户供应商导入文件(1).xlsx")).head(FirmCustomerAuxiliary.class).sheet().doReadSync();
    }
    /**
     * 读取新增到数据库的客户信息
     * @return List<FirmCustomerInfo> 列表数据
     * @author yzy
     * @date 2022/7/5 19:33
     */
    public static List<FirmCustomerAuxiliary> readInsertFirmCustomer(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220704\\dbInfo.xls")).head(FirmCustomerAuxiliary.class).sheet().doReadSync();
    }
    /**
     * 组装插入客户附属信息的SQL脚本
     * @param firmCustomerAuxiliary 客户附属信息
     * @return String sql
     * @author yzy
     * @date 2022/7/5 19:33
     */
    public static String createFirmCustomerAuxiliarySql(FirmCustomerAuxiliary firmCustomerAuxiliary){
        String sql = "INSERT INTO firm_customer_auxiliary ( id, customer_id, prcs_etps_sccd, cus_reg_no, full_name, prcs_etps_ciqno, madel_name, district_code, ciq_dest_code, master_cus_no, aeo_grade, interior_grade, deleted_flag, creater_id, creater_name, creater_org_id, creater_firm_id, create_time, modifier_id, modifier_name, modifier_org_id, modifier_firm_id, modify_time ) " +
                "VALUES ( UUID(), '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '', 0, 'A4A8E5B3-6E65-41F9-85F9-F2D79DE356E7', '勤达管理员', '', 'FBE66F7B-E914-4583-99BB-389779F60992', now(), 'A4A8E5B3-6E65-41F9-85F9-F2D79DE356E7','系统','','FBE66F7B-E914-4583-99BB-389779F60992', now() );";
        return StrUtil.format(sql,firmCustomerAuxiliary.getCustomerId(),firmCustomerAuxiliary.getPrcsEtpsSccd(),firmCustomerAuxiliary.getCusRegNo(),
                firmCustomerAuxiliary.getFullName(), firmCustomerAuxiliary.getPrcsEtpsCiqno(),firmCustomerAuxiliary.getMadelName(),firmCustomerAuxiliary.getDistrictCode(),
                firmCustomerAuxiliary.getCiqDestCode(),firmCustomerAuxiliary.getMasterCusNo(),firmCustomerAuxiliary.getAeoGrade());
    }
    /**
     * 客户附属信息转成SQL语句输出到SQL文件中
     * @author yzy
     * @date 2022/7/5 19:53
     */
    public static void doFirmCustomerAuxiliarySql(){
        //获取数据库中已存在的客户信息
        List<FirmCustomerAuxiliary> dbAuxiliaryList = readInsertFirmCustomer();
        //从Excel中解析出对应的客户信息
        List<FirmCustomerAuxiliary> firmCustomerAuxiliaries = readFirmCustomerAuxiliaryExcel().stream().filter(item->StrUtil.isNotBlank(item.getPrcsEtpsSccd())).map(
                item-> {
                    Optional<FirmCustomerAuxiliary> first = dbAuxiliaryList.stream().filter(dbitem -> StrUtil.equalsAnyIgnoreCase(dbitem.getRegisteredNo(), StrUtil.trim(item.getRegisteredNo()))).findFirst();
                    item.setCustomerId(first.isPresent()?first.get().getCustomerId():StrUtil.EMPTY);
                    return item;
                }
        ).filter(item->StrUtil.isNotBlank(item.getCustomerId())).collect(Collectors.toList());
        //组装sql语句
        List<String> sqlList = firmCustomerAuxiliaries.stream().map(item->createFirmCustomerAuxiliarySql(item)).collect(Collectors.toList());
        //输入到一个sql文件中
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220704\\out\\customerAuxiliary.sql");
        FileUtil.appendUtf8Lines(sqlList,new File(outFilePath));
    }

    public static void main(String[] args) {
        doFirmCustomerAuxiliarySql();
    }
}
